package com.mbc.hr.manager;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.NoResultException;

import com.mbc.common.CommonConstants.RecordStatus;
import com.mbc.common.exception.DataDuplicationException;
import com.mbc.common.exception.ForeignConflictException;
import com.mbc.common.manager.AbstractMgr;
import com.mbc.common.param.CriteriaParams;
import com.mbc.common.utils.KeyGenerator;
import com.mbc.hr.data.LeaveSchemeDataList;
import com.mbc.hr.entities.Employee;
import com.mbc.hr.entities.LeaveBalance;
import com.mbc.hr.entities.LeaveScheme;
import com.mbc.hr.entities.LeaveSchemeDetail;

public class LeaveSchemeMgr extends AbstractMgr {
	public LeaveSchemeMgr(){}
	
	public LeaveSchemeMgr(String sessionID){
		this.sessionId = sessionID;
	}
	
	public void create(LeaveScheme leaveScheme)throws Exception{
		openConnection();
		try {
			beginTransaction();
			leaveScheme.setLeaveSchemeID(KeyGenerator.generateKey(entityManager));
			entityManager.persist(leaveScheme);
			
			for (LeaveSchemeDetail l_detail : leaveScheme.getDetails()) {
				l_detail.setLeaveSchemeID(leaveScheme.getLeaveSchemeID());
				entityManager.persist(l_detail);
			}
			
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			if(DataDuplicationException.isUniqueKyeError(e))
				throw new DataDuplicationException(e.getMessage());
			else
				throw e;
		} finally{
			closeConnection();
		}
	}
	
	public void update(LeaveScheme leaveScheme)throws Exception{
		openConnection();
		try {
			beginTransaction();
			
			entityManager.merge(leaveScheme);
			
			entityManager.createNamedQuery("LeaveSchemeDetail.deleteByLeaveScheme")
				.setParameter("leaveSchemeID", leaveScheme.getLeaveSchemeID())
				.executeUpdate();
			for (LeaveSchemeDetail l_detail : leaveScheme.getDetails()) {
				l_detail.setLeaveSchemeID(leaveScheme.getLeaveSchemeID());
				entityManager.persist(l_detail);
			}
			
			List<Employee> l_empList = entityManager.createNamedQuery("Employee.findAllByLeaveSchemeID",Employee.class)
					.setParameter("leaveSchemeID", leaveScheme.getLeaveSchemeID())
					.setParameter("status", RecordStatus.delete)
					.getResultList();
			LeaveBalance l_leaveBalance;
			Calendar l_cal = Calendar.getInstance();
			l_cal.setTime(new Date());
			int l_year = l_cal.get(Calendar.YEAR);
			for (Employee l_emp : l_empList) {
				for (LeaveSchemeDetail l_detail : leaveScheme.getDetails()) {
					l_leaveBalance = null;
					try {
						l_leaveBalance = entityManager.createNamedQuery("LeaveBalance.findByEmployeeIdLeaveTypeYear", LeaveBalance.class)
								.setParameter("employeeID", l_emp.getEmployeeID())
								.setParameter("leaveTypeID", l_detail.getLeaveTypeID())
								.setParameter("year", l_year)
								.getSingleResult();
					} catch (NoResultException e) {}
					if(l_leaveBalance == null) {
						l_leaveBalance = new LeaveBalance();
						l_leaveBalance.setEmployeeID(l_emp.getEmployeeID());
						l_leaveBalance.setLeaveTypeID(l_detail.getLeaveTypeID());
						l_leaveBalance.setMaximum(l_detail.getDaysPerYear());
						l_leaveBalance.setTotal(0);
						l_leaveBalance.setYear(l_year);
						entityManager.persist(l_leaveBalance);
					}else {
						l_leaveBalance.setMaximum(l_detail.getDaysPerYear());
						entityManager.merge(l_leaveBalance);
					}
				}
			}
			
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void delete(long leaveSchemeID)throws Exception{
		openConnection();
		try {
			beginTransaction();
			entityManager.createNamedQuery("LeaveSchemeDetail.deleteByLeaveScheme")
				.setParameter("leaveSchemeID", leaveSchemeID)
				.executeUpdate();
			LeaveScheme l_leaveScheme = entityManager.find(LeaveScheme.class, leaveSchemeID);
			entityManager.remove(l_leaveScheme);
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			if(e.getCause().getCause().getCause() instanceof SQLException){
				if(ForeignConflictException.FOREIGN_CONFLICT_ERROR_CODE == ((SQLException)e.getCause().getCause().getCause()).getErrorCode())
					throw new ForeignConflictException(e);
				else
					throw e;
			}
			else
				throw e;
		} finally {
			closeConnection();
		}
	}
	
	public LeaveScheme find(long leaveSchemeID)throws Exception{
		openConnection();
		try {
			LeaveScheme l_leaveScheme = entityManager.find(LeaveScheme.class, leaveSchemeID);
			if(l_leaveScheme != null){
				List<Object[]> l_results = entityManager.createNamedQuery("LeaveSchemeDetail.findAllByLeaveSchemeWithLeaveTypeName", Object[].class)
						.setParameter("leaveSchemeID", leaveSchemeID)
						.getResultList();
				l_leaveScheme.setDetails(new ArrayList<LeaveSchemeDetail>());
				for (Object[] l_result : l_results) {
					LeaveSchemeDetail l_detail = (LeaveSchemeDetail)l_result[0];
					l_detail.setLeaveTypeName(l_result[1].toString());
					l_leaveScheme.getDetails().add(l_detail);
				}
			}
			return l_leaveScheme;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public List<LeaveScheme> findAll()throws Exception{
		openConnection();
		try {
			return entityManager.createNamedQuery("LeaveScheme.findAll", LeaveScheme.class).getResultList();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public List<LeaveSchemeDetail> findDetails(long leaveSchemeID)throws Exception{
		openConnection();
		try {
			List<Object[]> l_results = entityManager.createNamedQuery("LeaveSchemeDetail.findAllByLeaveSchemeWithLeaveTypeName", Object[].class)
					.setParameter("leaveSchemeID", leaveSchemeID)
					.getResultList();
			List<LeaveSchemeDetail> l_details = new ArrayList<LeaveSchemeDetail>();
			for (Object[] l_result : l_results) {
				LeaveSchemeDetail l_detail = (LeaveSchemeDetail)l_result[0];
				l_detail.setLeaveTypeName(l_result[1].toString());
				l_details.add(l_detail);
			}
			return l_details;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public LeaveSchemeDataList getLeaveSchemes(CriteriaParams cri)throws Exception{
		openConnection();
		try {
            String l_jpql = "select ls from LeaveScheme ls where ls.schemeCode like '%" + cri.getKeyword() + "%' or ls.description like '%" + cri.getKeyword() + "%'";
            
            List<LeaveScheme> l_entityList = entityManager.createQuery(l_jpql, LeaveScheme.class)
                    .setFirstResult(cri.getIndex())
                    .setMaxResults(cri.getSize())
                    .getResultList();
            
            LeaveSchemeDataList l_dataList = new LeaveSchemeDataList();
            l_dataList.setEntityList(l_entityList);
            
            if(cri.getIndex() == 0){
            	String l_countQuery = "select count(ls) from LeaveScheme ls where ls.schemeCode like '%" + cri.getKeyword() + "%' or ls.description like '%" + cri.getKeyword() + "%'";
            	l_dataList.setTotalRecord(Long.parseLong(entityManager.createQuery(l_countQuery).getSingleResult().toString()));
            }
			return l_dataList;
		} catch (Exception e) {
			throw e;
		} finally{
			closeConnection();
		}
	}
}
